package org.myutil;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class ExcelToListReader {

	/**
	 * 从 Excel 文件读取数据到 List 对象
	 * @param filePath Excel 文件路径
	 * @param clazz 目标对象类型
	 * @return List<T> 对象列表
	 */
	public static <T> List<T> readFromExcel(String filePath, Class<T> clazz)
			throws IOException, IllegalAccessException, InstantiationException {

		List<T> resultList = new ArrayList<>();

		FileInputStream fis = new FileInputStream(filePath);
		Workbook workbook = createWorkbook(filePath, fis);
		Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表

		// 获取表头（第一行）
		Row headerRow = sheet.getRow(0);
		if (headerRow == null) {
			workbook.close();
			fis.close();
			return resultList;
		}

		// 获取字段名映射
		String[] headers = new String[headerRow.getLastCellNum()];
		for (int i = 0; i < headerRow.getLastCellNum(); i++) {
			Cell cell = headerRow.getCell(i);
			headers[i] = cell != null ? cell.getStringCellValue() : "";
		}

		// 读取数据行
		int rowCount = sheet.getLastRowNum();
		Field[] fields = clazz.getDeclaredFields();

		for (int i = 1; i <= rowCount; i++) { // 从第二行开始读取数据
			Row row = sheet.getRow(i);
			if (row == null) continue;

			T obj = clazz.newInstance();

			for (int j = 0; j < headers.length && j < fields.length; j++) {
				Cell cell = row.getCell(j);
				if (cell == null) continue;

				// 根据字段名匹配设置值
				for (Field field : fields) {
					if (field.getName().equals(headers[j])) {
						field.setAccessible(true);
						Object cellValue = getCellValue(cell);
						setFieldValue(obj, field, cellValue);
						break;
					}
				}
			}

			resultList.add(obj);
		}

		workbook.close();
		fis.close();

		return resultList;
	}

	/**
	 * 根据文件扩展名创建相应的 Workbook
	 */
	private static Workbook createWorkbook(String filePath, FileInputStream fis) throws IOException {
		if (filePath.endsWith(".xlsx")) {
			return new XSSFWorkbook(fis);
		} else if (filePath.endsWith(".xls")) {
			return new HSSFWorkbook(fis);
		} else {
			throw new IllegalArgumentException("不支持的文件格式");
		}
	}

	/**
	 * 获取单元格的值
	 */
	private static Object getCellValue(Cell cell) {
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					return cell.getDateCellValue();
				} else {
					return cell.getNumericCellValue();
				}
			case Cell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue();
			case Cell.CELL_TYPE_BLANK:
				return "";
			default:
				return null;
		}
	}

	/**
	 * 设置对象字段值
	 */
	private static <T> void setFieldValue(T obj, Field field, Object value)
			throws IllegalAccessException {
		if (value == null) return;

		Class<?> fieldType = field.getType();

		if (fieldType == String.class) {
			field.set(obj, value.toString());
		} else if (fieldType == int.class || fieldType == Integer.class) {
			if (value instanceof Number) {
				field.set(obj, ((Number) value).intValue());
			} else {
				try {
					field.set(obj, Integer.parseInt(value.toString()));
				} catch (NumberFormatException e) {
					field.set(obj, 0);
				}
			}
		} else if (fieldType == long.class || fieldType == Long.class) {
			if (value instanceof Number) {
				field.set(obj, ((Number) value).longValue());
			} else {
				try {
					field.set(obj, Long.parseLong(value.toString()));
				} catch (NumberFormatException e) {
					field.set(obj, 0L);
				}
			}
		} else if (fieldType == double.class || fieldType == Double.class) {
			if (value instanceof Number) {
				field.set(obj, ((Number) value).doubleValue());
			} else {
				try {
					field.set(obj, Double.parseDouble(value.toString()));
				} catch (NumberFormatException e) {
					field.set(obj, 0.0);
				}
			}
		} else if (fieldType == boolean.class || fieldType == Boolean.class) {
			if (value instanceof Boolean) {
				field.set(obj, value);
			} else {
				field.set(obj, Boolean.parseBoolean(value.toString()));
			}
		} else if (fieldType == java.util.Date.class && value instanceof java.util.Date) {
			field.set(obj, value);
		} else {
			field.set(obj, value);
		}
	}
}